/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package sqlgenerators.search;

import entities.Search;
import entities.SearchRelocatingInfoRequerimentAndOperator;

/**
 *
 * @author damfercer
 */
public class RelocatingAndResitrictionsSQLGenerator implements SearchSqlGeneratorInterface{
    private String res;
    @Override
    public String getSqlFromSearch(Search search) {
        res="";
        if(!search.getSearchRelocatingInfoRequerimentAndOperatorList().isEmpty()){
            res+=" AND (";
            int i=0;
            for(SearchRelocatingInfoRequerimentAndOperator restriction: search.getSearchRelocatingInfoRequerimentAndOperatorList()){
                if(i==0){
                    res+="TRUE";
                    res+=generateSearchRelocatingInfoRequerimentAndOperatorSQL(restriction);
                }
                else{
                    res+=generateSearchRelocatingInfoRequerimentAndOperatorSQL(restriction);
                }
                i++;
            }
            res+=")";
        }
        return res;
    }
    
    private String generateSearchRelocatingInfoRequerimentAndOperatorSQL(SearchRelocatingInfoRequerimentAndOperator restr){
        String result="";
        if(restr.getCountry()!=null){
            result=" AND EXISTS(SELECT ID_CANDIDATE_RELOCATING_INFO FROM candidate_relocating_info WHERE candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE";
            result+=" AND candidate_relocating_info.COUNTRY="+restr.getCountry().getIdCountry();
            if(restr.getProvince()!=null){
                result+=" AND (candidate_relocating_info.PROVINCE IS NULL OR candidate_relocating_info.PROVINCE="+restr.getProvince().getIdProvince()+")";
            }
            else{
                result+=" AND candidate_relocating_info.PROVINCE IS NULL";
            }
            result+=")";
        }
        return result;
    }
}
